x

Help Please!What is missing from this updat script?

USE [Staples  POG Test]
GO
UPDATE Plano
SET Desc3 = (SELECT message_line_6 FROM Spaceman.dbo.NSW
WHERE Id = Name
UPDATE Plano
SET Desc4 = (SELECT segment_width/width FROM Spaceman.dbo.SECTION
WHERE Id  = Name
UPDATE Plano
SET Desc5 = (SELECT height FROM Spaceman.dbo.SECTION
WHERE Id = Name
UPDATE Plano
SET Custom1 = (SELECT depth FROM Spaceman.dbo.SECTION
WHERE Id = Name
UPDATE Plano
SET Custom2 = (SELECT width FROM Spaceman.dbo.SECTION
WHERE Id = Name
UPDATE Plano
SET Custom3 = (SELECT segment_width FROM Spaceman.dbo.SECTION
WHERE Id = Name
more ▼

asked Jul 13, 2012 at 11:11 AM in Default

callvey gravatar image

callvey
0 1 1 2

Thanks for that, Using you script, How can I add another table from DB2?
Jul 13, 2012 at 11:57 AM callvey
I am trying to update from 2 tables in DB2
Jul 13, 2012 at 12:00 PM callvey
Do you want to update columns from different tables? For better understanding use some dummy column and table names instead of Column and Table keyword. It will help us to give a precise answer.
Jul 13, 2012 at 12:01 PM Usman Butt

Yes I am using the Column and table for brevity and no I havnt posted the script more than once.

This is the error Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'UPDATE'.

USE [DB1] GO UPDATE tableA SET ColumnA = (SELECT columnA FROM DB2.dbo.tableA WHERE ID = Name UPDATE tableA SET ColumnB = (SELECT ColumnB FROM DB2.dbo.tableB WHERE Id = Name UPDATE tableA SET ColumnC = (SELECT ColumnC FROM DB2.dbo.tableB WHERE Id = Name UPDATE tableA SET ColumnD = (SELECT ColumnD FROM DB2.dbo.tableB WHERE Id = Name UPDATE tableA SET ColumnE = (SELECT ColumnE FROM DB2.dbo.tableB WHERE Id = Name
Jul 13, 2012 at 12:09 PM callvey

For that error I already told that there is a missing ")" after every "Name" column in your script.

If you want to update from 2 tables, then amend your question to reflect which columns you need from which table.
Jul 13, 2012 at 12:12 PM Usman Butt
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Assuming you are using "column" and "table" keywords for brevity and mistakenly posted the script more than once, there is a closing parentheses ")" missing after the "Name" Column.

Also are you sure that ID & Name have the same data type and are the intended columns to compare the values? Moreover, what error you are prompted with?

EDIT:

After re-formatting the question I realize that the OP wants to update multiple columns

@callvey You can certainly update multiple columns in one update statement like

UPDATE tableA
SET 
 ColumnA = [column],
 ColumnB = [column],
 ColumnC = [column],
 ColumnD = [column],
 ColumnE = [column], 
 ColumnF = [column]
FROM tableA INNER JOIN DB2.dbo.[TABLE]
ON ID = NAME

EDIT1:

According to the latest information

UPDATE Plano
SET 
 Desc3 = message_line_6
FROM Plano INNER JOIN Spaceman.dbo.NSW
ON ID = NAME

UPDATE Plano
SET 
 Desc4 = segment_width/width,
 Desc5 = height ,
 Custom1 = depth ,
 Custom2 = width , 
 Custom3 = segment_width
FROM Plano INNER JOIN Spaceman.dbo.SECTION
ON ID = NAME
more ▼

answered Jul 13, 2012 at 11:21 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thats brilliant it works great thanks,
Jul 13, 2012 at 01:17 PM callvey
@callvey Glad to know that it helped. Would you mind accepting the answer then? It will help the future users as well ;)
Jul 13, 2012 at 01:24 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x343
x129
x67

asked: Jul 13, 2012 at 11:11 AM

Seen: 969 times

Last Updated: Jul 13, 2012 at 01:49 PM