How to update and insert using joins

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

How to update and insert using joins

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-01-21 : 05:45:22

I have 2 tables that are joined using sgridguid. To select fields from table 2 I join the tables like this:

select * from strcell c, strgrid g where g.sgridguid = c.sgridguid and stoolguid = '{0140DB3A-EF83-48EF-BC80-00D01CBA63E4}' and lgridnumber = 1

How would I update and insert values into table 2?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 05:49:24

can u post ur tables structures, and which table has to be update and insert

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-01-21 : 06:00:53

quote:
Originally posted by bklr

can u post ur tables structures, and which table has to be update and insert

strGrid
stoolguid PK
lgridnumber PK
sgridguid
sname
sdescription

strCell
sgridguid PK
lrow PK
lcol PK
lfieldtype

I need to update strcell

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 06:06:14

update sc
set lfieldtype = sdescription
from strcell sc
inner join
strgrid sg on sg.sgridguid = sc.sgridguid and stoolguid = '{0140DB3A-EF83-48EF-BC80-00D01CBA63E4}' and lgridnumber = 1

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-01-21 : 06:29:06

quote:
Originally posted by bklr

update sc
set lfieldtype = sdescription
from strcell sc
inner join
strgrid sg on sg.sgridguid = sc.sgridguid and stoolguid = '{0140DB3A-EF83-48EF-BC80-00D01CBA63E4}' and lgridnumber = 1

Thants great thanks, I was getting close to this but kept getting errors so I used 2 select commands the first to get the sgridguid.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 06:36:33

quote:
Originally posted by insanepaul

quote:
Originally posted by bklr

update sc
set lfieldtype = sdescription
from strcell sc
inner join
strgrid sg on sg.sgridguid = sc.sgridguid and stoolguid = '{0140DB3A-EF83-48EF-BC80-00D01CBA63E4}' and lgridnumber = 1

Thants great thanks, I was getting close to this but kept getting errors so I used 2 select commands the first to get the sgridguid.

welcome

did ur problem has been solved r not

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-01-21 : 06:57:35

quote:
Originally posted by bklr

quote:
Originally posted by insanepaul

quote:
Originally posted by bklr

update sc
set lfieldtype = sdescription
from strcell sc
inner join
strgrid sg on sg.sgridguid = sc.sgridguid and stoolguid = '{0140DB3A-EF83-48EF-BC80-00D01CBA63E4}' and lgridnumber = 1

Thants great thanks, I was getting close to this but kept getting errors so I used 2 select commands the first to get the sgridguid.

welcome
did ur problem has been solved r not

yes it solved my problem thanks - i'm using it now. I'm now figuring out how to delete and insert

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-01-21 : 07:08:21

Can use the same syntax for delete as update


DELETE x
FROM
   tableA x
   JOIN tableB y ON y.[blah] = x.[blah]

For insert have to use


INSERT tableA (
   [col1]
   , [col2]
   )
SELECT
   x.[foo]
   , y.[bar]
FROM
   tableb x
   JOIN tablec y ON y.[tablebId] = x.[Id]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources