CODEWITHSUNDEEP
sql-serversql-server-2008sqlcommand
eqiz
eqiz

Reputation: 1591

Multiple Record Update based off Select Results

Here is my base SQL query in SQL Server 2008:

SELECT userID
FROM coreUsers 
INNER JOIN pluginSales ON coreUser.userID = pluginSales.userID
WHERE (coreUser.CurrentYear = '2014') 
  AND (pluginSales.PaidShipping = 'Yes') 
   OR (coreUser.IsContract = 'Yes')

Just tested on 12/19/2014 again, this returns 61 results.

I need to be able to update the pluginSales table column ShipppingDate for all the records that result from the above statement.

coreUser.userID is my unique key that auto increments.

pluginSales has a column ShippingDate that is just a standard varchar that I need update to '12/16/2014' if the record was set to 2014 (coreUser.CurrentYear) and they PaidShipping (pluginSales.PaidShipping) OR if they are a contracted customer (coreUser.IsContract)

EDIT #1

I tried the following...

UPDATE       pluginSales
SET                ShippingDate = ''
FROM            coreUsers AS cu INNER JOIN
                         pluginSales AS pis ON cu.userID = pis.userID CROSS JOIN
                         pluginSales
WHERE (cu.CurrentYear = '2014') AND (pis.PaidShipping = 'Yes' OR pis.IsContract = 'Yes')

Just tested again on 12/19/2014 this modified 554 records (my whole pluginSales database)

This affected ALLLLLL of my rows and not just the specific ones. Please advise.

Upvotes: 0

Views: 53

Answers (2)

amit_g
amit_g

Reputation: 31250

First run the select

SELECT *
FROM pluginSales PS
INNER JOIN coreUsers CU ON CU.userID = PS.userID
WHERE (CU.CurrentYear = '2014')
    AND (PS.PaidShipping = 'Yes')
    OR (CU.IsContract = 'Yes')

then change only first line to UPDATE ...

UPDATE pluginSales SET ShippingDate = '12/16/2014'
FROM pluginSales PS
INNER JOIN coreUsers CU ON CU.userID = PS.userID
WHERE (CU.CurrentYear = '2014')
    AND (PS.PaidShipping = 'Yes')
    OR (CU.IsContract = 'Yes')

Your Where clause has an OR. You may want to explicitly group using parenthesis ().

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can do this with an update and join:

UPDATE cu
    SET ShippingDate '12/16/2014'
FROM coreUsers cu INNER JOIN
     pluginSales pis
     ON cu.userID = pis.userID
WHERE (cu.CurrentYear = '2014' AND (pis.PaidShipping = 'Yes') OR (pis.IsContract = 'Yes');

You may actually want this where clause:

WHERE (cu.CurrentYear = '2014') AND (pis.PaidShipping = 'Yes' OR pis.IsContract = 'Yes');

Note that you shouldn't be storing dates in strings. Instead, you should be using the native date or datetime types. If you have to store dates as strings, you should use YYYY-MM-DD format, so operations such as order by and comparisons will work on the values.

Upvotes: 3

Related Questions