Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update query when joined tables are involved

Author  Topic 

meef
Posting Yak Master

113 Posts

Posted - 2013-09-30 : 12:57:49
I have a query written that filters on joined table data. The SELECT looks like this:

SELECT    *
FROM tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
ORDER BY a.bol_number DESC


If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:

UPDATE tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
SET accrueamt='1348'
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 13:01:01
You can update using a join and where clause filters - but the thing to keep in mind is that you can update only one table. The update cannot affect multiple tables. That does not seem to be a problem in your case because you are updating only tbl_bol. So the syntax would be like this:
UPDATE a SET a.accrueamt = '1348'
FROM tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
Go to Top of Page

meef
Posting Yak Master

113 Posts

Posted - 2013-09-30 : 13:04:41
Ah, ok, didn't know you could still use the FROM in an update. Thanks, that did the trick.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 13:33:36
Yes, you can use the from clause, but SQL purists and ANSI afficionados will try to discourage you from that - mainly because when the result of the join returns more than one row for a single row in the target table, and if the source column has different values in those multiple rows, it is unpredictable which of those values will be used to update.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 13:34:09
This is an example - Should the value in table #A be 20 or 30 after the update?
CREATE TABLE #A(id INT, val FLOAT);
CREATE TABLE #B(id INT, val FLOAT);

INSERT INTO #A VALUES (1,10);
INSERT INTO #B VALUES (1,20);
INSERT INTO #B VALUES (1,30);

UPDATE a SET val = b.val
FROM
#A a
INNER JOIN #B b ON a.id = b.id;

SELECT * FROM #A;

DROP TABLE #A;
DROP TABLE #B;
In your case, this is not an issue because you are updating the rows to a constant literal.
Go to Top of Page
   

- Advertisement -