Sql Update Multiple Fields from via a Select Statement

SQL Update Multiple Fields FROM via a SELECT Statement

Something like this should work (can't test it right now - from memory):

UPDATE SHIPMENT
SET
  OrgAddress1     = BD.OrgAddress1,
  OrgAddress2     = BD.OrgAddress2,
  OrgCity         = BD.OrgCity,
  OrgState        = BD.OrgState,
  OrgZip          = BD.OrgZip,
  DestAddress1    = BD.DestAddress1,
  DestAddress2    = BD.DestAddress2,
  DestCity        = BD.DestCity,
  DestState       = BD.DestState,
  DestZip         = BD.DestZip
FROM
   BookingDetails BD
WHERE 
   SHIPMENT.MyID2 = @MyID2
   AND
   BD.MyID = @MyID

Does that help?

How to update multiple columns in single update statement in DB2

The update statement in all versions of SQL looks like:

update table
    set col1 = expr1,
        col2 = expr2,
        . . .
        coln = exprn
    where some condition

So, the answer is that you separate the assignments using commas and don't repeat the set statement.

Update multiple columns Table from a select statement

As you tagged your question tsql, I assume that you are using sql-server.

In this case, you can have JOINs in your UPDATE statement. Based on the query you displayed, the syntax would look like :

UPDATE a
SET a.ucIIWBin1 = c.cBinLocationDescription
FROM WhseStk a
INNER JOIN StkItem b ON b.StockLink = a.WHStockLink
INNER JOIN _btblBINLocation c on c.idBinLocation = a.iBinLocationID 
WHERE a.WHWhseID = 5

As you mentionned that your queries were narrowed down, you might need to adapt the above example (especially, you mentionned that you need to update 3 fields while you show only one in your code).

How do I UPDATE from a SELECT in SQL Server?

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

updating multiple columns using case statement in sql server

You'll have to swap the syntax around. The case statement will be applied for every value you want to update...

UPDATE table SET
    pay1 = CASE WHEN @columnname IN('name1') THEN pay1 * 100 ELSE pay1 END,
    pay2 = CASE WHEN @columnname IN('name1', 'name2') THEN pay2 * 20 ELSE pay2 END,
    pay3 = CASE WHEN @columnname IN('name1', 'name2', 'name3') THEN pay3 * 100 ELSE pay3 END

It looks like you actually want is a if statement....

IF @columnname = 'name1'
    UPDATE table SET pay1 = pay1 * 100, pay2=pay2*20, pay3=pay3* 100

ELSE IF @ColumnName = 'name2'
    UPDATE table SET pay2 = pay2 * 20, pay3 = pay3 * 100

ELSE IF @ColumnName = 'name3'
    UPDATE table SET pay3 = pay3 * 100

Hope that helps

Update multiple rows using select statement

Run a select to make sure it is what you want

SELECT t1.value AS NEWVALUEFROMTABLE1,t2.value AS OLDVALUETABLE2,*
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

Update

UPDATE Table2
SET Value = t1.Value
FROM Table2 t2
INNER JOIN Table1 t1 on t1.ID = t2.ID

Also, consider using BEGIN TRAN so you can roll it back if needed, but make sure you COMMIT it when you are satisfied.

Using CASE within a single query to update multiple columns in SQL table

You can simply use either ISNULL or COALESCE

Example

UPDATE YourTableName
SET
ColumnName1 = ISNULL(ColumnName1,' '),
ColumnName2 = COALESCE(ColumnName2,' ')
WHERE <Condition>

or if you still wish to use CASE specify the Column name in the ELSE because otherwise, the Data will be set to NULL for all Not Null fields

UPDATE YourTableName
SET
ColumnName1 = CASE WHEN ColumnName1 IS NULL THEN '' ELSE ColumnName1 END
WHERE <Condition>

how do I write a select statement that will evaluate multiple fields to populate a new field value?

In SQL Server you would use the CASE expression.

I used the field names from your sample and you did not provide a table name so you will have to update the example to fit your needs.

SELECT CASE WHEN [CertDate] IS NOT NULL
                 AND [CertStatus] = 'Current'
                 AND [PassDue] = 'No' THEN 'ReadOnTime'
            WHEN [CertDate] IS NOT NULL
                 AND [CertStatus] = 'Current'
                 AND [PassDue] = 'Yes' THEN 'ReadLate'
            WHEN [CertDate] IS NULL
                 AND [CertStatus] = 'Pending'
                 AND [PassDue] = 'Yes' THEN 'UnReadLate'
            ELSE ''
       END AS [Status]
FROM   [YourTableName];

Updating Multiple Columns from another table - Need Oracle format

This is how I would do it. It might not be the best performance, but it works.

MERGE INTO PERSONS_TMP PT
USING ( 
    SELECT P.PERSON, P.JOB_TITLE, P.FIRST_NAME, P.LAST_NAME, P.FACILITY_ID 
    FROM PERSONS P) TMP
ON (PT.PERSON = TMP.PERSON)
WHEN MATCHED THEN 
UPDATE SET 
    PT.FACILITY_ID = TMP.FACILITY_ID, 
    PT.JOB_TITLE = TMP.JOB_TITLE,
    PT.FIRST_NAME = TMP.FIRST_NAME,
    PT.LAST_NAME = TMP.LAST_NAME;

The script above will update information in PERSONS_TMP table using data from PERSONS table. I believe in your case, you want it the other way around. So, please make sure you make the necessary changes before running the script.

You can add "WHEN NOT MATCHED THEN.... " clause to the above SQL in case you need to insert new records, if it does not exist.

---

Related Topics