Query Question - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Query Question

Author

Topic

nicka
Starting Member

28 Posts

Posted - 2002-06-01 : 16:20:50

UPDATE tblProductServicesSystem INNER JOIN tblProductServicesUpdate1 ON [tblProductServicesSystem].[ProductID]=[tblProductServicesUpdate1].[ProductID] SET tblProductServicesSystem.ProductName = [tblProductServicesUpdate1].[ProductName]

The above query is only updating EXISTING 'ProductID' How do you modify the query so that it also inserts also unmatched records from tblProductServicesUpdate1 into tblProductServicesSystem based on NEW 'ProductID' found in tblProductServicesUpdate1

ailuro
Starting Member

8 Posts

Posted - 2002-06-01 : 22:47:09

I'm not entirely sure I understand the question completely, so here's what I think you're doing:

You want to have a table that accepts Updates to ProductServices in the tblProductServicesUpdate1 and, when some criteria is met, move all of the new records from Update into the more permanent tblProductServicesSystem table, as well as update the ProductNames if they changed or not. Unfortunatly you can't do all that in a single statement (that I know of) so you have to settle for doing seperate UPDATE and INSERT statements.

For the INSERT, you can make it look like this:
INSERT INTO tblProductServicesSystem
(ProductID, ProductName)
SELECT ProductID, ProductName FROM tblProductServicesUpdate1 WHERE
ProductID NOT IN (SELECT ProductID FROM tblProductServicesSystem)

I think there is a better way to do it than NOT IN with a massive SELECT like that, but I can't remember how to do an exclusion off the top of my head :)

In any case, if ProductID in tblProductServicesSystem is an Identity column (the number is automatically generated), you'll find that the above doesn't work because SQL doesn't, by default, let you shove Identity values in. If this is the case, you can put the following before and after the INSERT statement:
SET IDENTITY_INSERT tblProductServicesSystem ON
...Do the INSERT here...
SET IDENTITY_INSERT tblProductServicesSystem OFF

And that should let you jam values in the identity column. The syntax may be a bit off, but if you do a search for IDENTITY_INSERT you can find out what it really should be fairly quickly.

Okay, enough rambling from me, I hope that helps!

Ailuro

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-02 : 09:09:49

I think ailuro was looking for . . .


INSERT INTO tblProductServicesSystem 
(ProductID, ProductName) 
SELECT ProductID, ProductName FROM tblProductServicesUpdate1 psu WHERE not exists (SELECT 1 FROM tblProductServicesSystem where ProductID = psu.productID)

It should also be noted that you UPDATE syntax (nicka) is malformed. The JOIN belongs in a FROM clause as an UPDATE can only modify one table . . .

Bottom line, the basic method for doing what you want is just as ailuro suggests. UPDATE FROM INNER JOIN then INSERT WHERE not exists.

<O>

ailuro
Starting Member

8 Posts

Posted - 2002-06-02 : 10:00:09

Ah, yes! Thanks. For some reason I manage to rarely have to use any sort of exclusion, so I never remember the 'not exists' syntax.

Just as a note, the UPDATE query nicka gave should work. It's a peculiarity of SQL Server, not standards compliant by any means.

Thanks again :)

-=Ailuro=-

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-02 : 13:25:12

quote:

Just as a note, the UPDATE query nicka gave should work. It's a peculiarity of SQL Server, not standards compliant by any means.

really? I'm not able to make it work on SQL 2k, SP2


create table Page47 (col1 int, col2 varchar(20))
create table Page47_2 (col1 int, col2 varchar(20))

insert Page47 values (1,'Page47')
insert Page47_2 values (1,'Page47_2')
go

update Page47 inner join Page47_2 on Page47.col1 = Page47_2.col1
set Page47.col2 = Page47_2.col2
go

quote:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

<O>

ailuro
Starting Member

8 Posts

Posted - 2002-06-02 : 19:06:20

Er...you're right, of course. Not even sure why I said that it would work. For some reason I got it confused with the DELETE FROM INNER JOIN, remembered the note in the help files about a variation from the SQL-92 syntax, and, in my little brain, expanded it to work with UPDATE.

Must be because DELETE and UPDATE...you know...perform such similar tasks. And stuff. Sorry for wasting your time :)

-=Ailuro=-

Nazim
A custom title

1408 Posts

Posted - 2002-06-03 : 01:27:14

How about this way

update p set set Page47.col2 = Page47_2.col2 from
Page47 p inner join Page47_2 on p.col1 = Page47_2.col1
go

quote:

update Page47 inner join Page47_2 on Page47.col1 = Page47_2.col1
set Page47.col2 = Page47_2.col2
go

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources