By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,226 Members | 1,032 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,226 IT Pros & Developers. It's quick & easy.

SQL - Update statement

P: n/a
I have two tables...Table1(name, date); Table2(appointment_name, name,
appointment_date).

I want to update appointment_date in Table2 with date from Table1
(business requirement). I am trying to write a Update statement, I
don't seem to get it:

UPDATE Table2 t2 SET appointment_date = (SELECT date FROM Table1 t1
WHERE t1.name = t2.name) -- wrong
UPDATE Table2 t2 SET appointment_date = (SELECT date FROM Table1 t1)
where t2.name = t1.name -- wrong

any help will be greatly appreciated...

Thanks

Oct 1 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
an*************@gmail.com wrote:
I have two tables...Table1(name, date); Table2(appointment_name, name,
appointment_date).

I want to update appointment_date in Table2 with date from Table1
(business requirement). I am trying to write a Update statement, I
don't seem to get it:

UPDATE Table2 t2 SET appointment_date = (SELECT date FROM Table1 t1
WHERE t1.name = t2.name) -- wrong
UPDATE Table2 t2 SET appointment_date = (SELECT date FROM Table1 t1)
where t2.name = t1.name -- wrong
UPDATE table2 t2 SET appointment_date = (select date from table1 t1
where t1.name = t2.name)
where exists(select 1 from table1 t1 where t1.name = t2.name)

or (on DB2 for LUW)
MERGE INTO table2 t2 using table1 t1 on t1.name = t2.name
when matched then update set appointment_date = date

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Oct 1 '07 #2

P: n/a
On Oct 1, 5:54 pm, Serge Rielau <srie...@ca.ibm.comwrote:
annecarterfr...@gmail.com wrote:
I have two tables...Table1(name, date); Table2(appointment_name, name,
appointment_date).
I want to update appointment_date in Table2 with date from Table1
(business requirement). I am trying to write a Update statement, I
don't seem to get it:
UPDATE Table2 t2 SET appointment_date = (SELECT date FROM Table1 t1
WHERE t1.name = t2.name) -- wrong
UPDATE Table2 t2 SET appointment_date = (SELECT date FROM Table1 t1)
where t2.name = t1.name -- wrong

UPDATE table2 t2 SET appointment_date = (select date from table1 t1
where t1.name = t2.name)
where exists(select 1 from table1 t1 where t1.name = t2.name)

or (on DB2 for LUW)
MERGE INTO table2 t2 using table1 t1 on t1.name = t2.name
when matched then update set appointment_date = date

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Thanks a lot!
UPDATE table2 t2 SET appointment_date = (select date from table1 t1
where t1.name = t2.name)
where exists(select 1 from table1 t1 where t1.name = t2.name)
Could you please tell me why we need "where exists(select 1 from
table1 t1 where t1.name = t2.name)" in the query?

Thanks again!

Oct 2 '07 #3

P: n/a
On Oct 2, 3:56 pm, "annecarterfr...@gmail.com"
<annecarterfr...@gmail.comwrote:
[...]
UPDATE table2 t2 SET appointment_date = (select date from table1 t1
where t1.name = t2.name)
where exists(select 1 from table1 t1 where t1.name = t2.name)

Could you please tell me why we need "where exists(select 1 from
table1 t1 where t1.name = t2.name)" in the query?
Assume:

UPDATE table2 t2 SET appointment_date = (select date from table1 t1
where t1.name = t2.name)

What rows in t2 is affected by this stmt compared to Serge's stmt?:

UPDATE table2 t2 SET appointment_date = (select date from table1 t1
where t1.name = t2.name)
where exists(select 1 from table1 t1 where t1.name = t2.name)

What is the new value for appointment_date in the rows affected by the
first stmt but not the second stmt?
HTH
/Lennart

Oct 2 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.