August 23, 2004 at 8:41 am
UPDATE TABLE_A TA
Set TA.op_datetime = TB.op_datetime
where TA.key_id = (SELECT TB.key_id FROM TB)
I'm trying to set the values in one table based on the values from another. I keep getting a syntax error.
August 23, 2004 at 9:29 am
check joins between the tables
August 23, 2004 at 9:56 am
Try something like this:
UPDATE TABLE_A TA
Set TA.op_datetime = (select TB.op_datetime
FROM TB
where TB.key_id = TA.key_id)
where TA.key_id in (SELECT TB.key_id FROM TB)
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
August 24, 2004 at 8:41 am
I suggest to complete the query...
UPDATE TABLE_A Set TABLE_A.op_datetime = TB.op_datetime
FROM TABLE_A TA
INNER JOIN TABLE_B TB
ON TA.key_id=TB.key_id
your first query was missing the 'FROM' clause...
BTW note pls the TABLE_A was specified both in UPDATE clause (w/o alias) and in FROM clause with alias for the JOIN clause.
HTH
Lv
August 24, 2004 at 9:02 pm
Luigi said you the first query was missing a FROM clause.
Also I don't believe you can use aliases in the initial UPDATE clause and you don't need it in the SET clause either, but you can in a FROM clause.
So I think: -
UPDATE TABLE_A TA
Set TA.op_datetime = TB.op_datetime
where TA.key_id = (SELECT TB.key_id FROM TB)
would become: -
UPDATE TABLE_A
SET op_datetime = TB.op_datetime
FROM TABLE_B TB
WHERE TABLE_A.key_id = TB.key_id
August 25, 2004 at 8:13 am
First, the syntax error is that you are missing the FROM.
You CAN have a table alias, but it's how you alias it that can be a problem (you do it correctly).
This is NOT correct:
UPDATE tablea
SET a.ID = b.ID
FROM tablea a, tableb b
<rest of code>
This IS correct:
UPDATE tablea a
SET a.ID = b.ID
FROM tablea, tableb b
<rest of code>
Even better is to use the actual JOIN syntax. But either way, you must use a FROM when you have more than one table in an UPDATE statement.
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply