SQL - Update statement

[annecarterfredi]

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

[Serge Rielau]

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

[annecarterfredi]

On Oct 1, 5:54 pm, Serge Rielau <[e-mail]:

[e-mail] 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!

[Lennart]

On Oct 2, 3:56 pm, "[e-mail]"
<[e-mail]:
[...]

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