Update query - SQL Server Forums

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

Author

Topic

BLURDY
Starting Member

5 Posts

Posted - 2010-02-09 : 12:47:32

If I have 2 tables and I do 3 select statments ie

Select * from a join
(Select * from a) a1 on
a.number=a1.number join (Select * from b)b1
ON a.number = b1.number

This will return 3 table which is fine.

However how would I go about doing an update on this
update a

set a1.test=a.test

where etc

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 12:51:26

Replace the initial SELECT * with

UPDATE A
SET ...

"A" needs to be an alias to a table that is the FROM clause or a JOIN - it cannot be a derived table - so would be fine in your example

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 12:54:31

[code]update a
set a.test=a1.test
from a
join b a1
on a.number = a1.number
where....
[/code]

BLURDY
Starting Member

5 Posts

Posted - 2010-02-09 : 12:56:53

that didn work
does anyone have 2 full tables they can throw up and we can go through this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 13:02:19

quote:
Originally posted by BLURDY

that didn work
does anyone have 2 full tables they can throw up and we can go through this

can you clearly state what your actual scenario is with some sample data?

BLURDY
Starting Member

5 Posts

Posted - 2010-02-10 : 13:06:41

tablea
size,code,details
10,1234,good

20,5678,bad

tableb
size,code,details,bestbefore,cost
10,1234,good,2010,0.00

10,1234,good,2011,10.00

20,5678,bad,2010,0.00

20,5678,bad,2011,10.00

if table a has the default value with no costs how can i change this around so it takes the ones with cost

i hope this is explained ok

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 13:09:06

quote:
Originally posted by BLURDY

tablea
size,code,details
10,1234,good

20,5678,bad

tableb
size,code,details,bestbefore,cost
10,1234,good,2010,0.00

10,1234,good,2011,10.00

20,5678,bad,2010,0.00

20,5678,bad,2011,10.00

if table a has the default value with no costs how can i change this around so it takes the ones with cost

i hope this is explained ok

Nope not quite ok

tablea doesnt have cost column. Hence I'm not sure what you mean by words in green above

------------------------------------------------------------------------------------------------------
SQL Server MVP

BLURDY
Starting Member

5 Posts

Posted - 2010-02-10 : 13:14:44

what i mean is that cost is not in table a but only in table b,but if i wanted to change the information around how would i do this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 13:17:35

quote:
Originally posted by BLURDY

what i mean is that cost is not in table a but only in table b,but if i wanted to change the information around how would i do this

what info you want to change around?
can you illustrate with some sample data as per below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources