开发者

Check for existing row before doing insert

Table1 columns:

clinic_code, schedule_date, schedule_time, source_id

Table2 columns:

clinic_code, schedule_date, schedule_time

There is a stored procedure that does:

1. Delete all records in Table1 where clinic_code='ABC' AND schedule_date=xyz 2. INSERT all records from table2 where table2.clinic_code='ABC' AND table2.schedule_date=xyz into table1.

I want to make a change in step 2.

*2. INSERT all records from table2 where table2.clinic_code='ABC' AND table2.schedule_date=xyz, but don't overwrite those rows in table1 where source_id=2.

Here's the original insert statement in step 2:

INSERT INTO table1 (col1, col2, col3)
SELECT table2.col1, table3.col2, table2.col3
FROM table2
INNER JOIN table3 ON table2.col3 = table3.开发者_如何学编程col5 AND etc...

Table names, and additional columns have been left out, if it would be helpful, I can put the exact number of columns.


I'm not sure I follow completely, because if you're inserting the rows into table1, table1.col3 won't have a value for those rows yet. If you just want to avoid having table1.col3 = 2, you could use a where clause like this:

INSERT INTO table1 (col1, col2, col3)
SELECT table2.col1, table3.col2, table2.col3
FROM table2
INNER JOIN table3 ON table2.col3 = table3.col5 AND etc...
WHERE table2.col3 <> 2

Which would avoid inserting the value 2 into table1.col3, since table2.col3 is where you're pulling that from. If I'm missing the point here let me know, but that should do it.

EDIT - given that you've said the records between table1 and table2 are similar, and assuming there's a column you could join on between the two tables, you could do something like this:

INSERT INTO table1 (col1, col2, col3)
SELECT table2.col1, table3.col2, table2.col3
FROM table2
INNER JOIN table3 ON table2.col3 = table3.col5 AND etc...
LEFT OUTER JOIN table1 t1 ON tl.colX = table2.colX
WHERE table1.col3 <> 2

The idea being that you've now included table1 in your SELECT and as such can also include it in your WHERE clause. (Forgive me if the aliasing syntax is off for other SQL flavors, I'm mostly experienced in T-SQL.)


Well I don't know which SQL you are using so here is a standard SQL script:

BEGIN TRANSACTION ;
       IF NOT EXISTS ( SELECT * FROM dbo.table1 WHERE   col3 = 2 )
       BEGIN ;
           ...your insert goes here
       END ;
COMMIT ;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜