Update w/ SELECT ???

Question

Post reply

Update w/ SELECT ???

jbalbo

SSCertifiable

Points: 7583
More actions
August 5, 2013 at 12:39 pm

#275860

Hi
Trying to update a bunch of records , figured I'd use the following by using an excel SS with concatenate..
I have a synax error somewhere but can't find it ??
UPDATE dbo.CLIENT_IDENTIFIER, (SELECT * FROM dbo.Client INNER JOIN dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION ON dbo.Client.OID = dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID INNER JOIN dbo.CLIENT_IDENTIFIER ON dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK = dbo.CLIENT_IDENTIFIER.OID WHERE(dbo.Client.ID ='123')
SET dbo.CLIENT_IDENTIFIER.client_identifier = 'xx071023' where dbo.CLIENT_IDENTIFIER.client_identifier ='xx071540' ;
Thanks
Joe

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jbalbo SSCertifiable Points: 7583 More actions · Answer 1

Sorry

This makes no sense...

What I am trying to do is

if ID = '123' CLient Table

set Identifer to 'xx123' if the identifer = 'xx233'

my ss looks like :

clientID OldIDentifier NewIdentifier

123........... xx123 ..............xx233

etc...

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

jbalbo (8/5/2013)
Hi
Trying to update a bunch of records , figured I'd use the following by using an excel SS with concatenate..
I have a synax error somewhere but can't find it ??
UPDATE dbo.CLIENT_IDENTIFIER, (SELECT * FROM dbo.Client INNER JOIN dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION ON dbo.Client.OID = dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID INNER JOIN dbo.CLIENT_IDENTIFIER ON dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK = dbo.CLIENT_IDENTIFIER.OID WHERE(dbo.Client.ID ='123')
SET dbo.CLIENT_IDENTIFIER.client_identifier = 'xx071023' where dbo.CLIENT_IDENTIFIER.client_identifier ='xx071540' ;
Thanks
Joe

This is more than a syntax error. It is a logic error. What are you trying to do with that subselect? It just doesn't make any sense there at all.

The basic form of an update is:

UPDATE

Set [Colmn] = [VALUE]

Are you trying to use a select statement to retrieve the values for your update statement? I can't quite make heads or tails of this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jbalbo SSCertifiable Points: 7583 More actions · Answer 3

so what I need to do is update the client_identifier table based on the client ID on the client table with a link table in between..

so my spreadsheet looks like

clientID OLD Identifier NewIdentifier

I figure to use excel to concatenate all teh values ?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

jbalbo (8/5/2013)
so what I need to do is update the client_identifier table based on the client ID on the client table with a link table in between..
so my spreadsheet looks like
clientID OLD Identifier NewIdentifier
I figure to use excel to concatenate all teh values ?

Keep in mind we can't see your screen and have no idea what your project or tables or structures look like.

Maybe you want something like this?

UPDATE dbo.CLIENT_IDENTIFIER

SET client_identifier = 'xx071023'

from dbo.Client

INNER JOIN dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION ON dbo.Client.OID = dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID

INNER JOIN dbo.CLIENT_IDENTIFIER ON dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK = dbo.CLIENT_IDENTIFIER.OID

WHERE dbo.Client.ID ='123'

I would recommend that you start using aliases in your queries, it makes things about a zillion times easier to read. Also, I noticed you have a column name that is the same as the table name. This will also cause untold amounts of pain.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jbalbo SSCertifiable Points: 7583 More actions · Answer 5

Thanks Sean

Sorry made it more complicated than it was definitely should be using aliases there are lots of tables in the DB with similar and weird names...

Thanks