August 31, 2015 at 3:05 pm
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.
August 31, 2015 at 3:08 pm
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.
August 31, 2015 at 3:10 pm
Lynn Pettis (8/31/2015)
All I see are two select statements.
Thanks. It's been updated.
August 31, 2015 at 3:19 pm
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
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 31, 2015 at 3:27 pm
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
August 31, 2015 at 3:30 pm
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;
August 31, 2015 at 3:44 pm
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.
August 31, 2015 at 3:44 pm
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."
August 31, 2015 at 3:48 pm
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;
September 1, 2015 at 7:58 am
Same error message.
September 1, 2015 at 8:53 am
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?
September 1, 2015 at 5:35 pm
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
September 1, 2015 at 5:39 pm
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply