SQL - Update statement

[annecarterfredi@gmail.com]

I have two tables...Table1 (name, date); Table2(appointm ent_name, name,
appointment_dat e).

I want to update appointment_dat e 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_dat e = (SELECT date FROM Table1 t1
WHERE t1.name = t2.name) -- wrong
UPDATE Table2 t2 SET appointment_dat e = (SELECT date FROM Table1 t1)
where t2.name = t1.name -- wrong

any help will be greatly appreciated...

Thanks

[Serge Rielau]

Re: SQL - Update statement

annecarterfredi @gmail.com wrote:

I have two tables...Table1 (name, date); Table2(appointm ent_name, name,
appointment_dat e).
>
I want to update appointment_dat e 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_dat e = (SELECT date FROM Table1 t1
WHERE t1.name = t2.name) -- wrong
UPDATE Table2 t2 SET appointment_dat e = (SELECT date FROM Table1 t1)
where t2.name = t1.name -- wrong

UPDATE table2 t2 SET appointment_dat e = (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_dat e = date

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[annecarterfredi@gmail.com]

Re: SQL - Update statement

On Oct 1, 5:54 pm, Serge Rielau <srie...@ca.ibm .comwrote:

annecarterfr... @gmail.com wrote:

I have two tables...Table1 (name, date); Table2(appointm ent_name, name,
appointment_dat e).

>

I want to update appointment_dat e 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_dat e = (SELECT date FROM Table1 t1
WHERE t1.name = t2.name) -- wrong
UPDATE Table2 t2 SET appointment_dat e = (SELECT date FROM Table1 t1)
where t2.name = t1.name -- wrong

>
UPDATE table2 t2 SET appointment_dat e = (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_dat e = date
>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks a lot!

UPDATE table2 t2 SET appointment_dat e = (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]

Re: SQL - Update statement

On Oct 2, 3:56 pm, "annecarterfr.. .@gmail.com"
<annecarterfr.. .@gmail.comwrot e:
[...]

UPDATE table2 t2 SET appointment_dat e = (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_dat e = (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_dat e = (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_dat e in the rows affected by the
first stmt but not the second stmt?


HTH
/Lennart