SQL code for Update using join conditions

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

SQL code for Update using join conditions

Author

Topic

180x2
Starting Member

3 Posts

Posted - 2011-01-08 : 17:31:32

New to SQL (Oracle)

Am trying to write code for sollowing scenario:

Have 3 tables
table1 (campaignid,promoflag)
table2 (campaignid,projectid,campaigndesc)
table3 (projectid,promoflag,projectstart,projectend)

I am to update table 1 promoflag with value from promoflag in table3

Update table1
set promoflag = table3.promoflag

I would like to make sure only appropriate record is updated therefore want to use where clause condition but the primary key for table1 and table3 are different, the only link can be found on table2.

I want to use condition where table1.campaignid=table2.campaignid and table2.projectid=table1.projectid

How would I go about this?

180x2

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 17:34:21

Sounds like homework

Update table1
SET promoflag = table3.promoflag
FROM table1
inner join table2 on table1.campaignid = table2.camapaignid
inner join table3 on table2.projectid = table3.projectID

Poor planning on your part does not constitute an emergency on my part.

180x2
Starting Member

3 Posts

Posted - 2011-01-08 : 17:59:54

Greatly appreciate the help.

Truly not homework, just a business user with limited SQL knowledge trying to find a solution to a problem.

Have never used an inner join within a condition before.

I seem to be getting a synatx error with your suggested statement. I completed the statement with a colon (;), yet the system returned an error:

Update UA_CAMPAIGNEXTATTR
SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE
FROM UA_CAMPAIGNEXTATTR
inner join UA_CAMPAIGN on UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID
inner join LMUK_PROJECT_AUDIENCE_GRID on UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID
Error at line 1
ORA-00933: SQL command not properly ended

180x2

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 19:32:53

Depends on what version of Oracle. I thought ANSI style joins would work for you.

Try the other join method:

Update UA_CAMPAIGNEXTATTR
SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE
FROM UA_CAMPAIGNEXTATTR,UA_CAMPAIGN,LMUK_PROJECT_AUDIENCE_GRID
WHERE
UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID
AND UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID;

Poor planning on your part does not constitute an emergency on my part.

180x2
Starting Member

3 Posts

Posted - 2011-01-08 : 20:54:31

Your second suggestion is the code I used at first and failed with the same error message:

SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE
FROM UA_CAMPAIGNEXTATTR,UA_CAMPAIGN,LMUK_PROJECT_AUDIENCE_GRID
WHERE
UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID
AND UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID
Error at line 2
ORA-00933: SQL command not properly ended

It appears to get block with the 'FROM' statement (was underlined in red)

Oracle version 10G

Thanks again
180x2

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-08 : 21:20:12

you may have to do something like:
Update UA_CAMPAIGNEXTATTR
SET CFPROMOTABLE =
( Select LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE
FROM UA_CAMPAIGNEXTATTR
inner join UA_CAMPAIGN on UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID
inner join LMUK_PROJECT_AUDIENCE_GRID on UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID)

But this would have to be a subquery that only returns 1 value, 1 row.

UPDATE FROM is not used in Oracle...I always forget that sh** since I am so used to MS SQL.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources