CODEWITHSUNDEEP
sql
John1776
John1776

Reputation: 58

Multiple Column Subquery Update... SQL

I just started three weeks ago at a warehouse database company and Im trying to learn this material after hours since this is my first true programming job (just graduated). I can not show you the code but I will show you what I am working on. Efficiency with this program is key.

What I currently have that needs optimizing.

UPDATE TableName1
SET aValue1 = (SELECT aValue2 FROM TableName1, TableName2 WHERE search through extensive string (not sure if you needed to know that last part))
WHERE bValue1 in (SELECT bValue2 FROM TableName1, TableName2 WHERE search through extensive string (not sure if you needed to know that last part))

Generally, I would be fine with this. But this is using heavily serialized data with millions of items to search through and efficiency is key.

The two select statements are very similar... so they want to see if there is anyway to condense this so the select statement runs only once and funnels the data from the WHERE(UPDATE) into the SET(UPDATE). I keep running into the issue that you can only send one Column value back.

Any suggestions? I hope this was clear. Thank you all very much. Have a great weekend too.

Upvotes: 2

Views: 62

Answers (2)

Mansoor
Mansoor

Reputation: 4192

UPDATE TableName1 t1 SET t1.aValue1 = t2.aValue2 FROM TableName2 t2 WHERE t2.whateverProperty = something;

Upvotes: 1

Travis Gockel
Travis Gockel

Reputation: 27633

If the contents of your WHERE clauses are the same, you should be able to use a full table reference to only select rows which would show up in a join.

UPDATE TableName1 tn1 INNER JOIN TableName2 tn2 ON tn1.bValue1 = tn2.bValue2
  SET tn1.aValue1 = tn2.aValue2
  WHERE tn2.whateverProperty = something;

Upvotes: 1

Related Questions