Update data using join tables.

Anon

Hi All,

My database has 2 tables. I'm trying to update the date in one of the tables and my condition must include a reference to my second table.

I've tried 2 different ways and neither of them work. I've read the maual and have gone through through many forums but I can't seem to work out this problem.

1.update table1 set table1.value1 = CONCAT(value1, '$value1') where table1.value2=(select table2.value2 from table2 where table2.value3='$value3') and table1.value3 = '$value3' and table1.value4= '$value4'})

update table1 set table1.value1 = CONCAT(value1, '$value1') where table1.value2=table2.value2 and table1.value3 = '$value3' and table1.value4= '$value4'})

thanks for your help,

Vivian

thepriest

update table1
set table1.value1 = CONCAT(value1, '$value1')
from table1, table2
where table1.value2=table2.value2
and table1.value3 = '$value3'
and table1.value4= '$value4'

this came from manual:

UPDATE table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]

Anon

Hi Stef,

I tried the script again, adding the from list and now I'm getting this error message.

DBD::mysql::st execute failed: You have an error in your SQL syntax near 'from .
Can't call method "errstr" on an undefined value at updateIMT.pl line 309.

thanks a bunch for helping me.

Vivian

thepriest

sorry, I use postgres, and thats how it works.

in my sql though its like this:

UPDATE table1 INNER JOIN table2
ON table1.value2 = table2.value2
SET table1.value1 = concat(table1.value1, '$value1')
where table1.value3 = '$value3'
and table 1.value4= '$value4'

hope this helps, stef