0

I have a query like this:

UPDATE t1 
SET t1.col1 = ( SELECT col1 FROM t2 WHERE <some_complex_conditions> ),
    t1.col2 = ( SELECT col2 FROM t2 WHERE <some_complex_conditions> )
WHERE id = :id;

As you see, I have to execute the same query twice, every time for one column. Also as I've mentioned, that SELECT query has some complex conditions which need lots of processing. Now I want to know, how can I handle the UPDATE statement to get the update those two columns by single SELECT statement?

Something like this:

SELECT col1, col2 FROM t2 WHERE <some_complex_conditions>

In other word, how can I use this ^ into the UPDATE statement?

Martin AJ
  • 6,261
  • 8
  • 53
  • 111

2 Answers2

0

see multi table syntax in manual and stackoverflow

aschoerk
  • 3,333
  • 2
  • 15
  • 29
0

It's possible to update joined table. I think that it should be possible to join select statement, but I'm not sure, but I think example below shows equivalent query:

UPDATE t1
LEFT JOIN t2
 -- joining condition, there can be the part <some complex condition>
 ON t1.id = t2.ref_id AND t2.col3 = 'whatever you want'
SET t1.col1 = t2.col1, t1.col2 = t2.col2
--additional condition like WHERE t2.col1 IS NOT NULL
Jirka Picek
  • 589
  • 5
  • 19