SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update query when joined tables are involved
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meef
Yak Posting Veteran

97 Posts

Posted - 09/30/2013 :  12:57:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 09/30/2013 :  13:01:01  Show Profile  Reply with Quote
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
Yak Posting Veteran

97 Posts

Posted - 09/30/2013 :  13:04:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 09/30/2013 :  13:33:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 09/30/2013 :  13:34:09  Show Profile  Reply with Quote
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.

Edited by - James K on 09/30/2013 13:36:12
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000