SQL query UPDATE SET - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query UPDATE SET

Author

Topic

OMEN42
Starting Member

8 Posts

Posted - 2014-06-12 : 10:51:44

Hi SQL Community

I am really impressed of the great help within this Forum.
That motivates me to start a new request

I have a Table within SQL 2008 called Instrument.
The following table columns will be affected:

-TAG
-CompanyID
-DepartmentID

In a first step, I added new values to several tables like Building Name = '20' with automatically created table ID=63 and Cost Center No =12345 with ID=76

In addition to this I have an Excel file with new Tags for existing ones.

That means:

If Tag of column A1 in excel file
Then Tag = value of B2,
CompanyID = ID of table "Company" WHERE name ='20' (DB ID = 63)
DepartmentId = ID of table "Department" where name ='123456' AND CompanyID = ID of table "Company" where name ='20' (DB ID = 76)

That means Query detects Value in A1 like ABCD compare with B2=EFGH and replace the existing value with EFGH

Furthermore in this row the new entry EFGH needs to be linked to the column CompanyId = 63 and DepartmentID = 76

Expected Result: Tag:12345 CompanyID=63 DepartmentID=76

I created the following query:

UPDATE Instrument SET (Tag, CompanyId, DepartmentId) = ('ABCD','102','XXX') WHERE (Tag, CompanyId, DepartmentId)= ('EFGH','63','76')

The result has to be: Tag = EFGH CompanyId = 63 DepartmentId = 76

But it didn't work. Error message: incorrect syntax near '('

To be honest, I am afraid this text is needlessly complicated written;-)

Many thanks Sven

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-12 : 11:42:51

I didn't read your whole post, but here's a stab at fixing the syntax:

UPDATE Instrument
SET Tag = 'ABCD', CompanyId = '102', DepartmentId = 'XXX'
WHERE Tag = 'EFGH' AND CompanyId = '63' AND DepartmentId = '76'

What are the data types for these 3 columns?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

OMEN42
Starting Member

8 Posts

Posted - 2014-06-13 : 05:59:18

EDIT to read first:

I had a missmatch within my query, now I tried this one:

SELECT Id, Tag, CompanyId, DepartmentId FROM Instrument
WHERE Tag IN ( '102-1-WSTATION1-ANEMOM',
'102-1-WSTATION1-BAROMETER',
'102-1-WSTATION1-HUMIDITY',
'102-1-WSTATION1-RAIN',
'102-1-WSTATION2-ANEMOM',
'102-1-WSTATION2-HUMIDITY',
'102-1-WSTATION2-RAIN');

UPDATE Instrument 
SET Tag = '20-O-WSTATION1-ANEMOM', CompanyId = '63', DepartmentId = '76' 
WHERE Id = '1578'

I could identify all needed Ids thie first result is fine. But I couldn´t find the right query to update all TAG in one step, only for each single ;)

Hi Tara!!!

Thanks for the script I will test it asap.

I added a new Building name (20)(type=varchar) within Table Company and I got back the int data type as an increment ID from SQL = 63.

I also added within table Department a cost center number as varchar and I got back an int ID = 76.

Now I have to replace several TAG data like "102-1-WSTATION1-ANEMOM" with "20-O-WSTATION1-ANEMOM" and has to be sure that this data linked to the Cost Center and Building name. Of course with the internal Database ID 63 and 76...I hope I make it a little bit clear for the moment?;)

Please find attached the original data. The green highlighted are the new data. I add the cost center , building and at the end I have to replace old Tag to the new one and need the connection to new building and Cost Center.

But I have update it with the internal SQL ID as I described it before or?!!

[URL=http://www.directupload.net]IMG]http://s7.directupload.net/images/140613/cfua4hdg.png[/IMG][/URL]

EDIT: unfortunately it didn´t work yet..I made three querys as precondition for the last one above. Now I am a little bit confused

This one ist fine:

SELECT     Id, Name, Language
FROM         Company
INSERT INTO Company (Name, Language)
VALUES  ('20', 'en-GB')

and also this one:

Select * from Department

 Insert into Department (Name, CompanyId)
 Values ('1-3000-19420' , 63);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources