insert

Question

Post reply

insert

Mike Levan

SSChampion

Points: 14201
More actions
November 8, 2007 at 1:33 pm

#180130

tableA(empid(PK),empno,last,first,jobno,jobname)
I have inserted data into tableA except for jobno,jobname which will be null for this insert
now i have into insert from tableB(1 row) into tableA with fields jobno,jobname for all the empid of the previous insert.
how can i do tht.?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Since the row are already inserted you need to update them.

_____________
Code for TallyGenerator

Mike Levan SSChampion Points: 14201 More actions · Answer 2

I dont have any link to the table to update, can i update by querying where field='' something like tht?

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

Mike Levan (11/9/2007)
I dont have any link to the table to update, can i update by querying where field='' something like tht?

This should do what you want

UPDATE tableA

SET jobno = b.jobno,

jobname = b.jobname

FROM tableA a,

tableB b

WHERE a.empid = b.empid

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

Greg Snidow (11/9/2007)
Mike Levan (11/9/2007)
I dont have any link to the table to update, can i update by querying where field='' something like tht?
This should do what you want
UPDATE tableA
SET jobno = b.jobno,
jobname = b.jobname
FROM tableA a,
tableB b
WHERE a.empid = b.empid
Greg

I just realized your tableB might be in a different database, in which case your from clause would be FROM tableA a, databasename.dbo.tableB b

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Greg Snidow (11/9/2007)
Mike Levan (11/9/2007)
I dont have any link to the table to update, can i update by querying where field='' something like tht?
This should do what you want
UPDATE tableA
SET jobno = b.jobno,
jobname = b.jobname
FROM tableA a,
tableB b
WHERE a.empid = b.empid
Greg

WRONG!!!

Greg, your script will update whole tableA with occasional values from tableB.

[Code]

UPDATE a

SET jobno = b.jobno,

jobname = b.jobname

FROM tableA a,

tableB b

WHERE a.empid = b.empid

[/Code]

_____________
Code for TallyGenerator

Greg Snidow SSCoach Points: 16108 More actions · Answer 6

Sergiy (11/9/2007)
WRONG!!!
Greg, your script will update whole tableA with occasional values from tableB.
[Code]
UPDATE a
SET jobno = b.jobno,
jobname = b.jobname
FROM tableA a,
tableB b
WHERE a.empid = b.empid
[/Code]

I don't understand Sergiy, why would this not work? I use UPDATE like this all the time and it seems to work fine.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

Not true, Sergiy... and if you look, although it works, UPDATE tablealias isn't even listed in 2k BOL...

Syntax
UPDATE
{
table_name WITH ( [ ...n ] )
| view_name
| rowset_function_limited
}
SET

Only time you really need UPDATE tablealias is when more the one instance of the target table is in the join...

Here's a test for Greg's code...

--DROP TABLE #TableA, #TableB

--===== Create the test tables

CREATE TABLE #TableA (EmpID INT PRIMARY KEY, JobNo INT, JobName VARCHAR(30))

CREATE TABLE #TableB (EmpID INT PRIMARY KEY, JobNo INT, JobName VARCHAR(30))

--===== Populate table A using Tally table input

INSERT INTO #TableA (EmpID, JobNo, JobName)

SELECT TOP 10

EmpID = N,

JobNo = N,

JobName = 'Original '+STR(N,2)

FROM dbo.Tally

--===== Populate table B using Tally table input

INSERT INTO #TableB (EmpID, JobNo, JobName)

SELECT TOP 10

EmpID = N,

JobNo = N*10,

JobName = 'From Table B '+STR(N*10,2)

FROM dbo.Tally

WHERE N%2 = 0

--===== Visual verification of current content of both tables

SELECT * FROM #TableA

SELECT * FROM #TableB

--===== Do Greg's update

UPDATE #TableA

SET JobNo = b.JobNo,

JobName = b.JobName

FROM #TableA a,

#TableB b

WHERE a.EmpID = b.EmpID

--===== Show the new content of table A

SELECT * FROM #TableA

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

I don't have a chance to run proper tests now, but I would not say this if one of my colleagues would not destroy production database by an UPDATE statement like this.

Funny story is she was tested it in DEV and it worked fine, but when she ran it in Production it happened.

I'm not sure what it was - different Service Pack version, or physical order of lines in DEV happened to be such as needed for good result, but I don't want even smallest chance of something like that happened to my database.

And it's not about aliased option vs. non-aliased. It's about consistency. If you aliased the table then use the alias everywhere you mention this table. If you go with no alias then don't use alias for this table anywhere in the query.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 9

Heh... yeah, agreed... I've seen folks destroy production with an UPDATE as well... but it was never with the proper form of update (like Greg's is) which must be "If the target of the update is joined with other tables, then the target of the update must also be in the FROM clause". It doesn't explicitly list that in BOL, but if you look at all the joined examples in BOL, they all meet that rule.

The way people get themselves in trouble is by not observing that rule... they try to do something silly like...

UPDATE tgtTable

SET somecolumn = b.somecolumn

FROM otherTable b

WHERE tgtTable.pkcolumn = b.pkcolumn

... notice in the above, that a join is created between the tgtTable and the otherTable, but the tgtTable is NOT identified in the FROM clause. While this code will usually work correctly on single threaded queries, if parallelism is spawned it could take (has happened in my shop) hours to resolve instead of seconds. Such a query slammed a 4 CPU processor into the stops (all 4 CPU's pegged at 100%) for about 2 hours. When we rewrote the query correctly (according to BOL) and the update took about 1 minute to resolve.

The BOL compliant form of the above bad code would be...

UPDATE tgtTable

SET somecolumn = b.somecolumn

FROM tgtTable a,

otherTable b

WHERE a.pkcolumn = b.pkcolumn

UPDATE tgtTable

SET somecolumn = b.somecolumn

FROM tgtTable a

INNER JOIN otherTable b

ON a.pkcolumn = b.pkcolumn

As I said, the "UPDATE tablealias" is actually NOT covered anywhere in 2k BOL... guess that makes it an "undocumented feature". Of course, I've got no problem with using such features especially if they work well and predictably, which it does.

And, I didn't mean to jump on ya about Greg's code... all I wanted to do was explain why Greg is NOT wrong... he used the correct and classic form of SQL Server's update with a join... and for sure, it beats the hell out of Oracle's requirement to use correlated subqueries in both the SET list and the WHERE clause just because Oracle's UPDATE has no FROM clause to use. (Just had to get my digs in on Oracle 😛 ).

So far as consistency goes... I absolutely agree... but what Greg did wasn't wrong and that was really my only point.

Heh... I'll shut up now...:hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 10

Mike Levan (11/8/2007)
tableA(empid(PK),empno,last,first,jobno,jobname)
I have inserted data into tableA except for jobno,jobname which will be null for this insert
now i have into insert from tableB(1 row) into tableA with fields jobno,jobname for all the empid of the previous insert.
how can i do tht.?

Back to the original question.

What I see as a possible problem is "for all empid of the previous insert". If you mean it literally, that is, you only want to enter jobno, jobname for those rows that were inserted by the last insert (and not those that were already present in the table before the last insert), then there is no solution. Let's hope that's not what you meant.

So, in tableB there is only one row present? Hmm.. could you post tableB structure and data? I'm not sure there is such column as empid at all... looks to me more like a jobs lookup table or something of that kind.