other tablesin the where clause of an Update/Delete..

[udeebs]

I had a basic question - how can I copy individual column data from another table into this table in my update clause in a single sQL statemetn?


Ex:
table_a
----------
id name other cols timestamp
-- ---- ---------- ---------
1 abc 8/20
2 pqr 8/21
3 def 8/21

table_b
----------
id name other cols timestamp
-- ---- ---------- ---------
1 abc 8/20
2 pqr 8/20
3 def 8/20

what i need to do is -- update Table b , set b."other cols" = a."other cols" where a.timestamp > 8/20.

** where "other cols" implies multiple columns.

Currently I am doing a separate insert with select clause to copy the subset of table a into a temp table and then using it in the update clause with multiple selects for each colm ->

update table_b
set col_a = select col_a from temptable_a
set col_b = select col_b from temptable_a
where
table_b.id in (select id from table_a where timestamp > 8/20)

really appreciate any help - thank you.

 

[JamesLean]

Is this what you need?

UPDATE tableb
SET cola = a.cola,
  colb = a.colb
FROM tableb b JOIN tablea a ON b.id = a.id
WHERE a.timestamp > '8/20'

--James

 

[udeebs]

Thanks - That may work -
Is that Ansi SQL (or ORacle) query - thanks again.

 

[JamesLean]

As far as I am aware, that is ANSI-compliant. It will definitely work on SQL Server - not sure about Oracle.

--James

 

[udeebs]

- does not seem to work on ORACLE -

Does anyone know the equivalent on ORACLE - or some other way to do this ? - please help

 

[JamesLean]

I take it you have used a valid value for comparing to the timestamp field? Mine was really just showing the syntax rather than the exact solution.

--James

 

[udeebs]

yes- I understand. I have shown pseudo sql queries in my post as well without bothering with the actual syntax.
we got really excited when we saw this syntac because it would totally solve our problem - but note sure oracle has something equivalent.

 

[manolito]

Hi there, I am assuming that the tables can be joined via id AND name. Try this:

update table_b
set (other_col_a,
other_col_b,
other_col_c)
= (select other_col_a,
other_col_b,
other_col_c
from table_a
where table_a.id = table_b.id
and table_a.name = table_b.name
and table_a.timestamp > table_b.timestamp);

let me know if it does not work.
Spaniard

 

[swampBoogie]

Using a from clause in update is not ANSI SQL compliant. It works in Sybase and Sql server only afaik.

 

[manolito]

swampBoogie, maybe you missed the previous postings, the problem is Oracle-SQL, therefore SELECT FROM.. is the only way to go.

 

[swampBoogie]

No I didn't. My answer was to JamesLean.

 

[udeebs]

Thanks for the info guys- this was really helpful.