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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-04-28 : 07:31:37
|
| Kazim Senoglu writes "Here is my question:I have the follwing 2 tables.BILL_HDR===========BUSINESS_UNIT INVOICE------------- -------US001 INV_AFRA01 INV_BAUS01 INV_CBILL_LINE===========BUSINESS_UNIT INVOICE LINE_SEQ_NUM INVOICE_LINE------------- ------- ------------ ------------US001 INV_A 1 0FRA01 INV_B 1 0FRA01 INV_B 2 0AUS01 INV_C 1 1I would like to write a standard UPDATE query that will update PS_BI_LINE table's INVOICE_LINE to 1 for those invoices with only one row in PS_BI_LINE table and INVOICE_LINE for that one row is 0(zero). For the data above the UPDATE query should only update INV_A. You cannot use PS_BI_LINE table in a subquery in the UPDATE statement.INV_B should not be updated because it has 2 rows.INV_C should not be updated because INVOICE_LINE is not zero.Thank You" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-28 : 08:10:33
|
| update BILL_LINEset INVOICE_LINE = 1from BILL_LINE b1where INVOICE_LINE = 0and 1 = (select count(*) from BILL_LINE b2 where b1.INVOICE = b2.INVOICE and b1.BUSINESS_UNIT = b2.BUSINESS_UNIT)but without a subquery (strange condition)update BILL_LINEset INVOICE_LINE = 1from BILL_LINE b1left outer join BILL_LINE b2on b1.INVOICE = b2.INVOICE and b1.BUSINESS_UNIT = b2.BUSINESS_UNITand b1.LINE_SEQ_NUM <> b2.LINE_SEQ_NUMwhere b1.INVOICE_LINE = 0and b2.INVOICE is nullnot sure what you mean by a standard update query. This syntax is a sql server extension so probably isn't what you want either.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 04/28/2003 08:12:04 |
 |
|
|
|
|
|
SQL - UPDATE statement