How to Perform Update Query that Involves Multiple Tables?

Question

How to Perform Update Query that Involves Multiple Tables?

SilhouetteBS

SSCommitted

Points: 1597
More actions
August 31, 2015 at 3:05 pm

#320151

I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237
When I try to convert it into an UPDATE statement, I receive the error message "Column "QUDDAT_DATA.QUDDAT-SRC-ID" cannot be found or is not specified for query". Here is the update query I try to run:
UPDATE "PUB"."NAME"
SET "INTERNET-ADDRESS" = 'bsmith@acme.com'
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?
The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.
Thank you for your help.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

bsmith 63193 (8/31/2015)
I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237
When I try to convert it into an UPDATE statement, I receive the error message "Column "QUDDAT_DATA.QUDDAT-SRC-ID" cannot be found or is not specified for query". Here is the update query I try to run:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that? Thank you for your help.

All I see are two select statements.

SilhouetteBS SSCommitted Points: 1597 More actions · Answer 2

Lynn Pettis (8/31/2015)
All I see are two select statements.

Thanks. It's been updated.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

You can add tables in your update statement and join them, as shown here:

update table1

set x = y

from table 2

where table1.pk = table2.fk

Lowell SSC Guru Points: 323504 More actions · Answer 4

this looks right to me, and is sytactically correct, but you didn't mentione what the new value was.

i would use the ansi 92 syntax to make it clear to my eyes:

UPDATE "QUDDAT_DATA"

SET "QUDDAT_DATA"."QUDDAT-INT" = 'This is the New Value'

--SELECT "QUDDAT_DATA"."QUDDAT-INT","NAME"."INTERNET-ADDRESS","QUDDAT_DATA"."QUDFLD-FIELD-ID","QUDDAT_DATA"."QUDTBL-TABLE-ID"

FROM "SKYWARD"."PUB"."NAME" "NAME",

"SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"

WHERE ( "NAME"."NAME-ID" = "QUDDAT_DATA"."QUDDAT-SRC-ID" )

AND "QUDDAT_DATA"."QUDTBL-TABLE-ID" = 0

AND "QUDDAT_DATA"."QUDFLD-FIELD-ID" = 16

AND "QUDDAT_DATA"."QUDDAT-INT" = 11237

--better syntax

UPDATE "QUDDAT_DATA"

SET "QUDDAT_DATA"."QUDDAT-INT" = 'This is the New Value'

--SELECT "QUDDAT_DATA"."QUDDAT-INT","NAME"."INTERNET-ADDRESS","QUDDAT_DATA"."QUDFLD-FIELD-ID","QUDDAT_DATA"."QUDTBL-TABLE-ID"

FROM "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"

INNER JOIN "SKYWARD"."PUB"."NAME" "NAME"

ON ( "NAME"."NAME-ID" = "QUDDAT_DATA"."QUDDAT-SRC-ID" )

WHERE"QUDDAT_DATA"."QUDTBL-TABLE-ID" = 0

AND "QUDDAT_DATA"."QUDFLD-FIELD-ID" = 16

AND "QUDDAT_DATA"."QUDDAT-INT" = 11237

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

The following should work:

update n SET

[INTERNET-ADDRESS] = 'bsmith@acme.com'

from

SKYWARD.PUB.NAME n

INNER JOIN SKYWARD.PUB.QUDDAT-DATA qd

on n.[NAME-ID] = qd.[QUDDAT-SRC-ID]

WHERE

qd.[QUDTBL-TABLE-ID] = 0 AND

qd.[QUDFLD-FIELD-ID] = 16 AND

qd.[QUDDAT-INT] = 11237;

SilhouetteBS SSCommitted Points: 1597 More actions · Answer 6

Lowell (8/31/2015)
this looks right to me, and is sytactically correct, but you didn't mentione what the new value was.
i would use the ansi 92 syntax to make it clear to my eyes:
UPDATE "QUDDAT_DATA"
SET "QUDDAT_DATA"."QUDDAT-INT" = 'This is the New Value'
--SELECT "QUDDAT_DATA"."QUDDAT-INT","NAME"."INTERNET-ADDRESS","QUDDAT_DATA"."QUDFLD-FIELD-ID","QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME",
"SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ( "NAME"."NAME-ID" = "QUDDAT_DATA"."QUDDAT-SRC-ID" )
AND "QUDDAT_DATA"."QUDTBL-TABLE-ID" = 0
AND "QUDDAT_DATA"."QUDFLD-FIELD-ID" = 16
AND "QUDDAT_DATA"."QUDDAT-INT" = 11237
--better syntax
UPDATE "QUDDAT_DATA"
SET "QUDDAT_DATA"."QUDDAT-INT" = 'This is the New Value'
--SELECT "QUDDAT_DATA"."QUDDAT-INT","NAME"."INTERNET-ADDRESS","QUDDAT_DATA"."QUDFLD-FIELD-ID","QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
INNER JOIN "SKYWARD"."PUB"."NAME" "NAME"
ON ( "NAME"."NAME-ID" = "QUDDAT_DATA"."QUDDAT-SRC-ID" )
WHERE"QUDDAT_DATA"."QUDTBL-TABLE-ID" = 0
AND "QUDDAT_DATA"."QUDFLD-FIELD-ID" = 16
AND "QUDDAT_DATA"."QUDDAT-INT" = 11237

I specified the value in the 2nd statement I posted. I am actually trying to update the INTERNET-ADDRESS column though and not the QUDDAT-INT column. Almost.

SilhouetteBS SSCommitted Points: 1597 More actions · Answer 7

Lynn Pettis (8/31/2015)
The following should work:
update n SET
[INTERNET-ADDRESS] = 'bsmith@acme.com'
from
SKYWARD.PUB.NAME n
INNER JOIN SKYWARD.PUB.QUDDAT-DATA qd
on n.[NAME-ID] = qd.[QUDDAT-SRC-ID]
WHERE
qd.[QUDTBL-TABLE-ID] = 0 AND
qd.[QUDFLD-FIELD-ID] = 16 AND
qd.[QUDDAT-INT] = 11237;

Thank you for the reply, but it doesn't like that one. It gives the error "Table/View/Synonym not found."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

bsmith 63193 (8/31/2015)
Lynn Pettis (8/31/2015)
The following should work:
update n SET
[INTERNET-ADDRESS] = 'bsmith@acme.com'
from
SKYWARD.PUB.NAME n
INNER JOIN SKYWARD.PUB.QUDDAT-DATA qd
on n.[NAME-ID] = qd.[QUDDAT-SRC-ID]
WHERE
qd.[QUDTBL-TABLE-ID] = 0 AND
qd.[QUDFLD-FIELD-ID] = 16 AND
qd.[QUDDAT-INT] = 11237;
Thank you for the reply, but it doesn't like that one. It gives the error "Table/View/Synonym not found."

Try this:

update n SET

[INTERNET-ADDRESS] = 'bsmith@acme.com'

from

SKYWARD.PUB.NAME n

INNER JOIN SKYWARD.PUB.[QUDDAT-DATA] qd

on n.[NAME-ID] = qd.[QUDDAT-SRC-ID]

WHERE

qd.[QUDTBL-TABLE-ID] = 0 AND

qd.[QUDFLD-FIELD-ID] = 16 AND

qd.[QUDDAT-INT] = 11237;

SilhouetteBS SSCommitted Points: 1597 More actions · Answer 9

SilhouetteBS

SSCommitted

Points: 1597

September 1, 2015 at 7:58 am

#1823635

Same error message.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

bsmith 63193 (9/1/2015)
Same error message.

You are going to have to provide the complete error message. Also, are any of these tables actually views?

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 11

Steve Jones - SSC Editor (8/31/2015)
You can add tables in your update statement and join them, as shown here:
update table1
set x = y
from table 2
where table1.pk = table2.fk

Ah.... be VERY careful folks. This is actually an "illegal" form of update and you won't find even a single example of it in Books Online. Use of this type of query can (very difficult to predict) cause multiple CPUs to slam to the wall for hours on something that should take just a second or two because of a problem similar to "halloweening" but with the added annoyance of a recompile for every row. I've fixed such things at many different sites in my travels and a couple of times for posters on these fine forums.

[font="Arial Black"]If you do a joined update, you MUST include the table that asthe object of the update in the FROM clause [/font]or you stand a chance of having the problem when you can least afford to have it. You should also get out of the habit of using the table name in the UPDATE clause.

So, the code above should look like the following...

UPDATE t1

SET t1.x = t2.y

FROM dbo.Table1 t1

JOIN dbo.Table2 t2

ON t1.pk = t2.fk

;

It's good that the later queries are taking the recommended shape.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 12

I agree with Lynn. Please post the entire error message exactly as it is returned. Also, have you actually verified that the objects exist by checking sys.objects?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)