SQL Update Fields of One Table from Fields of Another One

update columns values with column of another table based on condition

Something like this should do it :

UPDATE table1 
   SET table1.Price = table2.price 
   FROM table1  INNER JOIN  table2 ON table1.id = table2.id

You can also try this:

UPDATE table1 
   SET price=(SELECT price FROM table2 WHERE table1.id=table2.id);

Update multiple columns of table based on another table sql

The exakt SQL command will of course depend on the query which produces your second table. Assuming, this query would just be "SELECT * FROM yourtable2", you can do following update command to achieve your goal:

UPDATE yourtable 
SET 
column2 = x.column2, 
column4 = x.column4
FROM (SELECT * FROM yourtable2) x
WHERE yourtable.column1 = x.column1
AND yourtable.column5 = x.column5

Here you see this is working (in case the table "yourtable2" provides the correct data): db<>fiddle
So, you can replace the "SELECT FROM yourtable2" by your query and it will work.

Update values from one column in same table to another in SQL Server

This works for me

select * from stuff

update stuff
set TYPE1 = TYPE2
where TYPE1 is null;

update stuff
set TYPE1 = TYPE2
where TYPE1 ='Blank';

select * from stuff

Update date field of one table from the value taken from another table

[TL;DR] Use a MERGE statement:

MERGE INTO Table1 dst
USING Table2 src
ON ( src.invoiced_value = dst.invoiced_value )
WHEN MATCHED THEN
  UPDATE SET invoiced_date = TRUNC( src.invoiced_date ) + INTERVAL '3:56:24' HOUR TO SECOND;

---

Can i use the following query?

No, an UPDATE statement does not have a FROM clause. You would need to use a correlated sub-query or a MERGE statement.

can i append the date like this to_date(e.invoiced_date ||' 3:56:24', 'MM/DD/YYYY HH:MI:SS') ?.

Maybe ... but you should not do it this way. TO_DATE( string_value, format_model ) takes a string as the first argument (and the || string concatenation operator also requires string arguments to concatenate) so your e.invoiced_date will be implicitly converted from a DATE to a string and your expression is effectively:

to_date(
  TO_CHAR(
    e.invoiced_date,
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
  ) || ' 3:56:24',
  'MM/DD/YYYY HH:MI:SS'
)

If your NLS_DATE_FORMAT session parameter is MM/DD/YYYY then your query will work. If it is something different then your query will either raise an exception or work but give incorrect results. Since NLS_DATE_FORMAT is a session parameter and each user can set it to whatever value they want then you should not rely on this to be consistent.

Instead, add an interval literal to the date (which does not require any conversions to-or-from a string):

TRUNC( src.invoiced_date ) + INTERVAL '3:56:24' HOUR TO SECOND

Or explicitly convert the date to a string in the correct format:

TO_DATE( TO_CHAR( e.invoiced_date, 'MM/DD/YYYY' ) || ' 3:56:24', 'MM/DD/YYYY HH24:MI:SS' )

SQL update one column from another column in another table

According to MySQL documentation, to do a cross table update, you can't use a join (like in other databases), but instead use a where clause:

http://dev.mysql.com/doc/refman/5.0/en/update.html

I think something like this should work:

UPDATE User_Settings, Contacts
    SET User_Settings.Contact_ID = Contacts.ID
    WHERE User_Settings.Account_ID = Contacts.Account_ID

---

Related Topics